package db;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;

import dto.Paper;
import dto.Researcher;

public class DAOFacade {
	
	public void guardarInvestigador(Researcher r) {
		PreparedStatement statement;
		try {
			if (r.getID_DB() == null) {
				statement = Database.getPreparedStatement("INSERT INTO \"INVESTIGADOR\" (\"ID_CONICET\", \"NOMBRE\", \"CATEGORIA\", \"DISCIPLINA_CIENTIFICA\", \"DISCIPLINA_DESAGREGADA\", \"CAMPO_DE_APLICACION\", \"ESPECIALIDAD\", \"AREA\", \"DIRECTOR\", \"CODIRECTOR\") VALUES (?,?,?,?,?,?,?,?,?,?);");
			} else {
				statement = Database.getPreparedStatement("UPDATE \"INVESTIGADOR\" SET \"ID_CONICET\" = ?, \"NOMBRE\" = ?, \"CATEGORIA\" = ?, \"DISCIPLINA_CIENTIFICA\" = ?, \"DISCIPLINA_DESAGREGADA\" = ?, \"CAMPO_DE_APLICACION\" = ?, \"ESPECIALIDAD\" = ?, \"AREA\" = ?, \"DIRECTOR\" = ?, \"CODIRECTOR\" = ? WHERE \"ID\" = ?;");
				statement.setInt(11, r.getID_DB());
			}
			statement.setInt(1, r.getID());
			statement.setString(2, r.getName());
			statement.setString(3, r.getCategory());
			statement.setString(4, r.getDisciplinaCientifica());
			statement.setString(5, r.getDisciplinaDesagregada());
			statement.setString(6, r.getCampoDeAplicacion());
			statement.setString(7, r.getSpeciality());
			statement.setString(8, r.getTopic());
			if (r.getDirector() != null) {
				statement.setInt(9, r.getDirector().getID_DB());
			} else {
				statement.setInt(9, -1);
			}
			if (r.getDirector() != null) {
				statement.setInt(10, r.getCodirector().getID_DB());
			} else {
				statement.setInt(10, -1);
			}
			statement.execute();
			if (r.getID_DB() == null) {
				ResultSet generatedKeys = statement.getGeneratedKeys();
				if (generatedKeys != null && generatedKeys.next()) {
					r.setID_DB(generatedKeys.getInt(1));
				}
			}
			statement.close();
			guardarTitulos(r.getTitles(), r.getID_DB());
			guardarLugaresDeTrabajo(r.getWorkPlaces(), r.getID_DB());
			guardarArticulos(r.getPapers());
			guardarAlias(r.getAlias(), r.getID_DB());
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	private void guardarLugaresDeTrabajo(LinkedList<String> workPlaces, Integer id) {
		eliminarLugaresDeTrabajo(id);
		for (String s : workPlaces){
			insertLugaresDeTrabajo(s, id);
		}
	}

	private void guardarTitulos(LinkedList<String> titles, Integer id) {
		eliminarTitulos(id);
		for (String s : titles){
			insertTitulo(s, id);
		}
	}
	
	private void eliminarLugaresDeTrabajo(Integer id) {
		PreparedStatement statement;
		try {
			statement = Database.getPreparedStatement("DELETE FROM \"LUGAR_DE_TRABAJO\" WHERE \"ID_INVESTIGADOR\" = ?;");
			statement.setInt(1, id);
			statement.execute();
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	private void insertLugaresDeTrabajo(String s, Integer id) {
		PreparedStatement statement;
		try {
			statement = Database.getPreparedStatement("INSERT INTO \"LUGAR_DE_TRABAJO\" (\"ID_INVESTIGADOR\", \"LUGAR_DE_TRABAJO\") VALUES (?,?);");
			statement.setInt(1, id);
			statement.setString(2, s);
			statement.execute();
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	private void eliminarTitulos(Integer id) {
		PreparedStatement statement;
		try {
			statement = Database.getPreparedStatement("DELETE FROM \"TITULO\" WHERE \"ID_INVESTIGADOR\" = ?;");
			statement.setInt(1, id);
			statement.execute();
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	private void insertTitulo(String s, Integer id) {
		PreparedStatement statement;
		try {
			statement = Database.getPreparedStatement("INSERT INTO \"TITULO\" (\"ID_INVESTIGADOR\", \"TITULO\") VALUES (?,?);");
			statement.setInt(1, id);
			statement.setString(2, s);
			statement.execute();
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void guardarArticulo(Paper p){
		PreparedStatement statement;
		try {
			if (p.getID_DB() == null){
				statement = Database.getPreparedStatement("INSERT INTO \"ARTICULO\" (\"ID_CONICET\", \"TITULO\", \"REVISTA\", \"EDITORIAL\", \"REFERENCIAS\", \"ISN\", \"RESUMEN\") VALUES (?,?,?,?,?,?,?);");
			} else {
				statement = Database.getPreparedStatement("UPDATE \"ARTICULO\" SET \"ID_CONICET\" = ?, \"TITULO\" = ?, \"REVISTA\" = ?, \"EDITORIAL\" = ?, \"REFERENCIAS\" = ?, \"ISN\" = ?, \"RESUMEN\" = ? WHERE ID = ?;");
				statement.setInt(8, p.getID_DB());
			}
			statement.setInt(1, p.getID_CONICET());
			statement.setString(2, p.getTitle());
			statement.setString(3, p.getMagazine());
			statement.setString(4, p.getEditorial());
			statement.setString(5, p.getReferences());
			statement.setString(6, p.getIsn());
			statement.setString(7, p.getResume());
			statement.execute();
			ResultSet generatedKeys = statement.getGeneratedKeys();
			if (generatedKeys != null && generatedKeys.next()){
				p.setID_DB(generatedKeys.getInt(1));
			}
			statement.close();
			guardarRelaciones(p);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	private void guardarArticulos(LinkedList<Paper> papers) {
		for (Paper p : papers){
			guardarArticulo(p);
		}
	}
	
	private void guardarRelaciones(Paper p) {
		eliminarRelacionesArticulo(p.getID_DB());
		for (Researcher r : p.getAuthors()){
			insertRelacionPaperResearcher(r.getID_DB(), p.getID_DB());
		}
	}
	
	public void eliminarInvestigador(Researcher r){
		PreparedStatement statement;
		try {
			statement = Database.getPreparedStatement("DELETE FROM \"INVESTIGADOR\" WHERE \"ID\" = ?;");
			statement.setInt(1, r.getID_DB());
			statement.execute();
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	private void eliminarRelacionesArticulo(int id){
		PreparedStatement statement;
		try {
			statement = Database.getPreparedStatement("DELETE FROM \"INVESTIGADOR_ARTICULO\" WHERE \"ID_ARTICULO\" = ?;");
			statement.setInt(1, id);
			statement.execute();
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	private void insertRelacionPaperResearcher(int idInvestigador, int idArticulo){
		PreparedStatement statement;
		try {
			statement = Database.getPreparedStatement("INSERT INTO \"INVESTIGADOR_ARTICULO\" (\"ID_INVESTIGADOR\", \"ID_ARTICULO\") VALUES (?,?);");
			statement.setInt(1, idInvestigador);
			statement.setInt(2, idArticulo);
			statement.execute();
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public Collection<Researcher> selectResearchers(){
		PreparedStatement statement;
		HashMap<Integer, Researcher> researcher = new HashMap<Integer, Researcher>();
		try {
			statement = Database.getPreparedStatement("SELECT * FROM \"INVESTIGADOR\";");
			statement.execute();
			ResultSet r = statement.getResultSet();
			while (r.next()){
				Researcher res = new Researcher();
				res.setID_DB(r.getInt("ID"));
				res.setID(r.getInt("ID_CONICET"));
				res.setName(r.getString("NOMBRE"));
				res.setCategory(r.getString("CATEGORIA"));
				res.setDisciplinaCientifica(r.getString("DISCIPLINA_CIENTIFICA"));
				res.setDisciplinaDesagregada(r.getString("DISCIPLINA_DESAGREGADA"));
				res.setCampoDeAplicacion(r.getString("CAMPO_DE_APLICACION"));
				res.setSpeciality(r.getString("ESPECIALIDAD"));
				res.setTopic(r.getString("AREA"));
				
				Integer id = r.getInt("DIRECTOR");
				if (id != -1){
					Researcher dir = new Researcher();
					dir.setID_DB(id);
					res.setDirector(dir);
				}
				id = r.getInt("CODIRECTOR");
				if (id != -1){
					Researcher codir = new Researcher();
					codir.setID_DB(id);
					res.setCodirector(codir);
				}
				researcher.put(res.getID_DB(), res);
			}
			statement.close();
			
			for (Researcher res : researcher.values()){
				Researcher dir = res.getDirector();
				if (dir != null){
					res.setDirector(researcher.get(dir.getID_DB()));
				}
				Researcher codir = res.getCodirector();
				if (codir != null){
					res.setCodirector(researcher.get(codir.getID_DB()));
				}
				res.agregarAlias(cargarAlias(res.getID_DB()));
				res.agregarTitulos(cargarTitulos(res.getID_DB()));
				res.agregarWorkPlaces(cargarLugaresDeTrabajo(res.getID_DB()));
			}
			cargarPapers(researcher);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return researcher.values();
	}
	
	private LinkedList<String> cargarTitulos(Integer id_DB) {
		LinkedList<String> toReturn = new LinkedList<String>();
		PreparedStatement statement;
		try {
			statement = Database.getPreparedStatement("SELECT * FROM \"TITULO\" WHERE \"ID_INVESTIGADOR\" = ?;");
			statement.setInt(1, id_DB);
			statement.execute();
			ResultSet r = statement.getResultSet();
			while (r.next()){
				toReturn.add((r.getString("TITULO")));
			}
			statement.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return toReturn;
	}

	private LinkedList<String> cargarLugaresDeTrabajo(Integer id_DB) {
		LinkedList<String> toReturn = new LinkedList<String>();
		PreparedStatement statement;
		try {
			statement = Database.getPreparedStatement("SELECT * FROM \"LUGAR_DE_TRABAJO\" WHERE \"ID_INVESTIGADOR\" = ?;");
			statement.setInt(1, id_DB);
			statement.execute();
			ResultSet r = statement.getResultSet();
			while (r.next()){
				toReturn.add((r.getString("LUGAR_DE_TRABAJO")));
			}
			statement.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return toReturn;
	}

	private LinkedList<Paper> cargarPapers(HashMap<Integer, Researcher> researchers) {
		LinkedList<Paper> toReturn = new LinkedList<Paper>();
		PreparedStatement statement;
		try {
			statement = Database.getPreparedStatement("SELECT * FROM \"ARTICULO\";");
			statement.execute();
			ResultSet r = statement.getResultSet();
			while (r.next()){
				Paper p = new Paper();
				p.setID_DB(r.getInt("ID"));
				p.setID_CONICET(r.getInt("ID_CONICET"));
				p.setTitle(r.getString("TITULO"));
				p.setMagazine(r.getString("REVISTA"));
				p.setEditorial(r.getString("EDITORIAL"));
				p.setReferences(r.getString("REFERENCIAS"));
				p.setIsn(r.getString("ISN"));
				p.setResume(r.getString("RESUMEN"));
				for (Integer id : getResearchersIdsForPaper(p.getID_DB())){
					Researcher author = researchers.get(id);
					if (author != null){
						author.agregarArticulo(p);
						p.agregarAutor(author);
					}
				}
			}
			statement.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return toReturn;
	}
	
	private LinkedList<Integer> getResearchersIdsForPaper(Integer paperID){
		LinkedList<Integer> toReturn = new LinkedList<Integer>();
		PreparedStatement statement;
		try {
			statement = Database.getPreparedStatement("SELECT * FROM \"INVESTIGADOR_ARTICULO\" WHERE \"ID_ARTICULO\" = ?;");
			statement.setInt(1, paperID);
			statement.execute();
			ResultSet r = statement.getResultSet();
			while (r.next()){
				toReturn.add((r.getInt("ID_INVESTIGADOR")));
			}
			statement.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return toReturn;
	}

	public Researcher buscarResercher(List<Researcher> l, int id){
		if (id == -1){
			return null;
		}
		for (Researcher r : l){
			if (r.getID_DB() == id){
				return r;
			}
		}
		return null;
	}
	
	public LinkedList<Paper> selectPaper(){
		return null;
		
	}
	
	private void eliminarAlias(int id){
		PreparedStatement statement;
		try {
			statement = Database.getPreparedStatement("DELETE FROM \"ALIAS\" WHERE \"ID_INVESTIGADOR\" = ?;");
			statement.setInt(1, id);
			statement.execute();
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	private List<String> cargarAlias(int id){
		LinkedList<String> toReturn = new LinkedList<String>();
		PreparedStatement statement;
		try {
			statement = Database.getPreparedStatement("SELECT * FROM \"ALIAS\" WHERE \"ID_INVESTIGADOR\" = ?;");
			statement.setInt(1, id);
			statement.execute();
			ResultSet r = statement.getResultSet();
			while (r.next()){
				toReturn.add((r.getString("ALIAS")));
			}
			statement.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return toReturn;
	}
	
	private void insertAlias(String alias, int id){
		PreparedStatement statement;
		try {
			statement = Database.getPreparedStatement("INSERT INTO \"ALIAS\" (\"ID_INVESTIGADOR\", \"ALIAS\") VALUES (?,?);");
			statement.setInt(1, id);
			statement.setString(2, alias);
			statement.execute();
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	private void guardarAlias(Collection<String> l, int id){
		eliminarAlias(id);
		for (String s : l){
			insertAlias(s, id);
		}
	}
	
	public void combinarInvestigadores(int from, int to){
		PreparedStatement statement;
		try {statement = Database.getPreparedStatement("UPDATE \"ALIAS\" SET \"ID_INVESTIGADOR\" = ? WHERE \"ID_INVESTIGADOR\" = ?;");
			statement.setInt(1, to);
			statement.setInt(2, from);
			statement.execute();
			statement.close();
			
			statement = Database.getPreparedStatement("UPDATE \"TITULO\" SET \"ID_INVESTIGADOR\" = ? WHERE \"ID_INVESTIGADOR\" = ?;");
			statement.setInt(1, to);
			statement.setInt(2, from);
			statement.execute();
			statement.close();
			
			statement = Database.getPreparedStatement("UPDATE \"LUGAR_DE_TRABAJO\" SET \"ID_INVESTIGADOR\" = ? WHERE \"ID_INVESTIGADOR\" = ?;");
			statement.setInt(1, to);
			statement.setInt(2, from);
			statement.execute();
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
